﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SQLite;

namespace LibrarySysPrototype
{
    public partial class Form1 : Form
    {
        //Works with Framework version 3.5, does not work with 4.0.
        //DB Connection is hard coded, could be problem if computer uses different letter for path of USB.
        SQLiteConnection libraryConnection = new SQLiteConnection("data source=E:\\librarysys.s3db");
        AutoCompleteStringCollection namesCollection = new AutoCompleteStringCollection();

        public Form1()
        {
            InitializeComponent();
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            SQLiteCommand FillTeachersCommand = new SQLiteCommand();
            FillTeachersCommand.Connection = libraryConnection;
            FillTeachersCommand.CommandType = CommandType.Text;
            FillTeachersCommand.CommandText = "SELECT FirstName, LastName FROM Teachers";
            FillTeachersCommand.Connection.Open();
            SQLiteDataReader reader = FillTeachersCommand.ExecuteReader();

            while (reader.Read())
            {
                TeacherNameDropDown.Items.Add(reader.GetString(0) + " " + reader.GetString(1));
            }
            FillTeachersCommand.Connection.Close();

            SQLiteCommand GetNamesCommand = new SQLiteCommand();
            GetNamesCommand.Connection = libraryConnection;
            GetNamesCommand.CommandType = CommandType.Text;
            GetNamesCommand.CommandText = "SELECT FirstName, LastName FROM Students";
            GetNamesCommand.Connection.Open();
            SQLiteDataReader reader2 = GetNamesCommand.ExecuteReader();

            while (reader2.Read())
            {
                namesCollection.Add(reader2.GetString(0) + " " + reader2.GetString(1));
            }
            GetNamesCommand.Connection.Close();

            StudentNameTextBox.AutoCompleteMode = AutoCompleteMode.Suggest;
            StudentNameTextBox.AutoCompleteSource = AutoCompleteSource.CustomSource;
            StudentNameTextBox.AutoCompleteCustomSource = namesCollection;
        }

        private void InsertTeachers(string m_FirstName, string m_LastName, int m_RoomNum)
        {
            SQLiteCommand InsertTeachersCommand = new SQLiteCommand();
            InsertTeachersCommand.Connection = libraryConnection;
            InsertTeachersCommand.CommandType = CommandType.Text;
            InsertTeachersCommand.CommandText = "INSERT INTO `Teachers`(`FirstName`, `LastName`, `RoomNum`)" +
                                             "VALUES ('Kelly', 'Gonzalez', '201')";
            try
            {
                InsertTeachersCommand.Connection.Open();
                InsertTeachersCommand.ExecuteNonQuery();
                InsertTeachersCommand.Connection.Close();
                MessageBox.Show("NEW TEACHER INSERTED SUCCESSFULLY!");
            }
            catch (SQLiteException)
            {
                MessageBox.Show("SQLITE DB ERROR. INSERT WAS NOT COMPLETED");
            }
        }

        private void StudentNameTextBox_Validated(object sender, EventArgs e)
        {
            string StudentName = StudentNameTextBox.Text;
            string[] names = StudentName.Split(' ');

            SQLiteCommand GetNamesCommand2 = new SQLiteCommand();
            GetNamesCommand2.Connection = libraryConnection;
            GetNamesCommand2.CommandType = CommandType.Text;
            GetNamesCommand2.CommandText = "SELECT Students.FirstName, Teachers.FirstName, Teachers.LastName "+
                                          "FROM Students "+
                                          "INNER JOIN Teachers ON Students.Teacher = Teachers.Id "+
                                          "WHERE Students.FirstName IN (@StudentName)";

            GetNamesCommand2.Parameters.Add(new SQLiteParameter("@StudentName", names[0].ToString()));
           
            GetNamesCommand2.Connection.Open();
            SQLiteDataReader reader3 = GetNamesCommand2.ExecuteReader();

            while (reader3.Read())
            {
                TeacherNameDropDown.SelectedText = reader3.GetString(1) + " "+ reader3.GetString(2);
            }
            GetNamesCommand2.Connection.Close();
        }
    }
}